package com.qqqq.dao.impl;


import com.qqqq.dao.StudentDao;
import com.qqqq.domain.SignIn;
import com.qqqq.domain.Student;
import com.qqqq.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {

    @Override
    public Student getStudentByNum(int stuNumber) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "select * from student where stu_number=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, String.valueOf(stuNumber));
        ResultSet rs = ps.executeQuery();
        List<Student> list = getStudentList(rs);
        System.out.println(list);
        rs.close();
        ps.close();
        conn.close();
        return !list.isEmpty() ? list.get(0) : null;
    }

    @Override
    public int addStudentSign(int id) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "insert into sign_in (stu_id,sign_in_time) values(?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ps.setString(2, String.valueOf(LocalDateTime.now()));
        int result = ps.executeUpdate();
        ps.close();
        conn.close();
        return result;
    }

    @Override
    public List<SignIn> pageSignIn(int stuId, int pageNum, int pageSize) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "select * from sign_in where stu_id=? limit ?,?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, stuId);
        ps.setInt(2, (pageNum - 1) * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<SignIn> list = getSignInList(rs);
        rs.close();
        ps.close();
        conn.close();
        return list;
    }

    @Override
    public int countSignIn(int id) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "select count(id) as total from sign_in where stu_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        int total = 0;
        if (rs.next()) {
            total = rs.getInt("total");
        }
        ps.close();
        conn.close();
        return total;
    }

    @Override
    public int addStudent(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "insert into student (stu_number,stu_name,stu_major_id,stu_class_id,sex,password) values(?,?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, student.getStuNumber());
        ps.setString(2, student.getStuName());
        ps.setInt(3, student.getStuMajorId());
        ps.setInt(4, student.getStuClassId());
        ps.setInt(5, student.getSex());
        ps.setString(6, student.getPassword());
        int result = ps.executeUpdate();
        ps.close();
        conn.close();
        return result;
    }

    @Override
    public int updateStudent(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "update student set stu_number = ?,stu_name = ?,stu_major_id=?,stu_class_id=?,sex=?,destroy_time = ?,password=? where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, student.getStuNumber());
        ps.setString(2, student.getStuName());
        ps.setInt(3, student.getStuMajorId());
        ps.setInt(4, student.getStuClassId());
        ps.setInt(5, student.getSex());
        ps.setDate(6, new java.sql.Date(student.getDestroyTime().getTime()));
        ps.setString(7, student.getPassword());
        ps.setInt(8, student.getId());
        int result = ps.executeUpdate();
        ps.close();
        conn.close();
        return result;
    }

    @Override
    public int deleteStudent(int id) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "delete from student where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        int result = ps.executeUpdate();
        ps.close();
        conn.close();
        return result;
    }

    @Override
    public Student getStudentById(int id) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "select * from student where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        System.out.println(rs);
        List<Student> list = getStudentList(rs);
        rs.close();
        ps.close();
        conn.close();
        if (!list.isEmpty()) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<Student> pageGetStudent(Integer stuMajorId, Integer stuClassId, int pageNum, int pageSize) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql;
        if (stuMajorId == null || stuClassId == null) {
            sql = "select * from student limit ?,?";
        } else {
            sql = "select * from student where stu_major_id = ? and stu_class_id = ? limit ?,?";
        }
        PreparedStatement ps = conn.prepareStatement(sql);
        if (stuMajorId == null || stuClassId == null) {
            ps.setInt(1, (pageNum - 1) * pageSize);
            ps.setInt(2, pageSize);
        } else {
            ps.setInt(1, stuMajorId);
            ps.setInt(2, stuClassId);
            ps.setInt(3, (pageNum - 1) * pageSize);
            ps.setInt(4, pageSize);
        }
        ResultSet rs = ps.executeQuery();
        List<Student> list = getStudentList(rs);
        rs.close();
        ps.close();
        conn.close();
        return list;
    }

    @Override
    public int countGetStudent(Integer stuMajorId, Integer stuClassId) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "select count(*) as total from student where stu_major_id=? and stu_class_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, stuMajorId);
        ps.setInt(2, stuClassId);
        ResultSet rs = ps.executeQuery();
        int total = 0;
        if (rs.next()) {
            total = rs.getInt("total");
        }
        ps.close();
        conn.close();
        return total;
    }


    private List<Student> getStudentList(ResultSet rs) throws SQLException {
        List<Student> list = new ArrayList<Student>();
        while (rs.next()) {
            list.add(new Student(
                    rs.getInt("id"),
                    rs.getInt("stu_number"),
                    rs.getString("stu_name"),
                    rs.getInt("stu_major_id"),
                    rs.getInt("stu_class_id"),
                    rs.getInt("sex"),
                    rs.getString("password")
            ));
        }
        return list;
    }

    private List<SignIn> getSignInList(ResultSet rs) throws SQLException {
        List<SignIn> list = new ArrayList<SignIn>();
        while (rs.next()) {
            list.add(new SignIn(
                    rs.getInt("id"),
                    rs.getInt("stu_id"),
                    rs.getDate("sign_in_time")
            ));
        }
        return list;
    }
}
